from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

app = Flask(__name__)
# 配置
# 配置mysql链接字符串
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:mysql@127.0.0.1:3306/toutiao'
# True 会追踪数据的变化，所以会有性能的损失。后面会废弃掉，所以设置为False
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = True

db = SQLAlchemy(app)

class User(db.Model):
    """
    用户基本信息
    """
    __tablename__ = 'user_basic'

    class STATUS:
        ENABLE = 1
        DISABLE = 0

    id = db.Column('user_id', db.Integer, primary_key=True, doc='用户ID')
    mobile = db.Column(db.String, doc='手机号')
    password = db.Column(db.String, doc='密码')
    name = db.Column('user_name', db.String, doc='昵称')
    profile_photo = db.Column(db.String, doc='头像')
    last_login = db.Column(db.DateTime, doc='最后登录时间')
    is_media = db.Column(db.Boolean, default=False, doc='是否是自媒体')
    is_verified = db.Column(db.Boolean, default=False, doc='是否实名认证')
    introduction = db.Column(db.String, doc='简介')
    certificate = db.Column(db.String, doc='认证')
    article_count = db.Column(db.Integer, default=0, doc='发帖数')
    following_count = db.Column(db.Integer, default=0, doc='关注的人数')
    fans_count = db.Column(db.Integer, default=0, doc='被关注的人数（粉丝数）')
    like_count = db.Column(db.Integer, default=0, doc='累计点赞人数')
    read_count = db.Column(db.Integer, default=0, doc='累计阅读人数')

    account = db.Column(db.String, doc='账号')
    email = db.Column(db.String, doc='邮箱')
    status = db.Column(db.Integer, default=1, doc='状态，是否可用')

    # 使用db.relationship创建关系属性
    # 此属性只是为了方便查询，在数据库表中并没有对应的字段。
    # 参数: 关联的类名称(字符串的形式)
    followings = db.relationship('Relation', primaryjoin='User.id==foreign(Relation.user_id)', lazy='dynamic')
    fans = db.relationship('Relation', primaryjoin='User.id==foreign(Relation.target_user_id)')

class Relation(db.Model):
    """
    用户关系表
    """
    __tablename__ = 'user_relation'

    class RELATION:
        DELETE = 0
        # 关注
        FOLLOW = 1
        # 黑名单
        BLACKLIST = 2

    id = db.Column('relation_id', db.Integer, primary_key=True, doc='主键ID')
    user_id = db.Column(db.Integer,  doc='用户ID')
    target_user_id = db.Column(db.Integer, doc='目标用户ID')

    # 获取目标用户对象
    target_user = db.relationship('User', primaryjoin='Relation.target_user_id==foreign(User.id)', uselist=False)
    relation = db.Column(db.Integer, doc='关系')
    ctime = db.Column('create_time', db.DateTime, default=datetime.now, doc='创建时间')
    utime = db.Column('update_time', db.DateTime, default=datetime.now, onupdate=datetime.now, doc='更新时间')

@app.route('/')
def index():
    return 'index'

if __name__ == '__main__':
    # query = User.query
    # print(query)
    # users = query.filter(User.mobile.startswith('13')).filter(User.name.endswith('2841')).all()
    # for user in users:
    #     print(user.id, user.mobile, user.name)

    # 常用思路,多条件查询
    # name = 'laowang'
    # mobile = '13423682841'
    # if name:
    #     query = User.query.filter(User.mobile == mobile)
    # users = query.all()

    user = User.query.get(1)
    # 发生子查询
    # 获取的是用户关注了哪些用户
    print('关注列表')
    for u in user.followings:
        print(u.user_id, u.target_user_id, u.target_user.name)

    # 加上lazy参数后:
    # print('关注列表')
    # 返回的是query对象
    # 此时才是所有的关注列表
    # sql: select * from user_relation where user_id=1 and relation=1;
    followings = user.followings.filter(Relation.relation == Relation.RELATION.FOLLOW).all()


